﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Collections.Specialized;

namespace MonitorProvider.Prototype.FileReader
{
    internal class DefaultXLSFileReader : FileReader.FileReaderBase
    {
        private const string xlsConnstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";
        private const string xlsxConnstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=Yes;IMEX=1\";";
        private const string sqlString = "Select * from [{0}]";
        private string _sheetName = string.Empty;

        public override void Initialize(FileType fileType, FileMonitor.Configuration.Prototype.FileProcessConfiguration fileProcessConfig)
        {
            base.Initialize(fileType, fileProcessConfig);
        }
        public DefaultXLSFileReader() : base() { }
       public override object GetFileProcessedResult(string fileName, System.Data.DataTable dtScheml, Guid batchId)
       {
            using (OleDbConnection dbCon = new OleDbConnection())
            {
                string connstring = System.IO.Path.GetExtension(fileName).EndsWith("xlsx") ? xlsxConnstring : xlsConnstring;
                dbCon.ConnectionString = string.Format(connstring, fileName);
                string[] strTableNames = null;

                if (string.IsNullOrEmpty(this._sheetName))
                {
                    dbCon.Open();
                    try
                    {
                        //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等  
                        DataTable dtSheetName = dbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

                        //包含excel中表名的字符串数组



                        for (int k = dtSheetName.Rows.Count - 1; k >= 0; k--)
                        {
                            if (!(dtSheetName.Rows[k]["TABLE_NAME"].ToString().EndsWith("$") || dtSheetName.Rows[k]["TABLE_NAME"].ToString().EndsWith("$'")))
                            {
                                dtSheetName.Rows.RemoveAt(k);
                            }
                        }
                        strTableNames = new string[dtSheetName.Rows.Count];
                        for (int k = 0; k < dtSheetName.Rows.Count; k++)
                        {
                            strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
                        }
                    }
                    finally
                    {
                        dbCon.Close();
                    }
                }

                DataSet rtnds = new DataSet();
                if (strTableNames == null || strTableNames.Length == 0)
                {
                    strTableNames = new string[1];
                    strTableNames[0] = string.IsNullOrEmpty(this._sheetName) ? "sheet1$" : this._sheetName;
                }

                using (OleDbCommand dbCom = new OleDbCommand())
                {
                    foreach (string sheetname in strTableNames)
                    {
                        dbCom.CommandText = string.Format(sqlString, sheetname);
                        dbCom.Connection = dbCon;
                        OleDbDataAdapter dbAda = new OleDbDataAdapter(dbCom);
                        dbAda.Fill(rtnds, sheetname.EndsWith("$") ? sheetname.Remove(sheetname.Length - 1) : sheetname);
                        foreach (DataColumn dc in rtnds.Tables[rtnds.Tables.Count - 1].Columns)
                        {
                            int index = dc.ColumnName.LastIndexOf("/");
                            if (index >= 0)
                            {
                                dc.ColumnName = dc.ColumnName.Remove(0, index + 1).Trim();
                            }
                            else
                            {
                                dc.ColumnName = dc.ColumnName.Trim();
                            }
                        }

                    }
                }

                foreach (DataTable dt in rtnds.Tables)
                {
                    dt.Columns.Add("BatchID", typeof(string));
                    foreach (DataRow dr in dt.Rows)
                    {
                        dr["BatchID"] = batchId.ToString();
                    }
                    
                }
                return rtnds;
            }

        }
    }
}
